package scct.hys.dao;

import scct.hys.model.User;
import scct.hys.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {


    public static List<User> search(int id, String name){
        List<User> users=new ArrayList<>();
        Connection conn=DBUtils.getConnection();
        String sql="select * from cm_user where name+"+id+"=? value+"+name+"=?";
        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.setString(2,name);

            ResultSet rs=pstmt.executeQuery();
            if (rs.next()){
                id=rs.getInt(1);
                name=rs.getString(2);
                String pass=rs.getNString("password");
                String gender=rs.getString("gender");
                int age=rs.getInt("age");
                String mobile=rs.getNString("mobile");
                users= (List<User>) new User(id,name,pass, age, gender, mobile);
            }
        }catch (Exception e){
           e.printStackTrace();
        }
        return users;
    }

    public User Login(String name, String pass){
        User user = null;
        Connection conn = DBUtils.getConnection();
        String sql = "select * from cm_user where name=? and password=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setString(2, pass);

            ResultSet rs= pstmt.executeQuery();
            if (rs.next()) {
                int id=rs.getInt("id");
                 name=rs.getString("name");
                 pass=rs.getNString("password");
                 String gender=rs.getString("gender");
                int age=rs.getInt("age");
                String mobile=rs.getNString("mobile");
                user = new User(id,name,pass, age, gender, mobile);
            }
            DBUtils.closeConnection(conn);

        }catch(Exception e){
            e.printStackTrace();
        }
        return user;
    }
    public static List<User> getAllUser()  {

        List<User> users=new ArrayList<>();
        Connection conn=DBUtils.getConnection();
        String sql="select * from cm_user";

        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            ResultSet rs=pstmt.executeQuery();
            while (rs.next()){
                int id=rs.getInt("id");
                String username=rs.getString("name");
                String password=rs.getString("password");
                String gender=rs.getString("gender");
                int age=rs.getInt("age");
                String mobile=rs.getString("mobile");
                users.add(new User(id,username,password, age, gender, mobile));
            }
            DBUtils.closeConnection(conn);
        }catch (SQLException e){
            throw new RuntimeException(e);
        }
        return users;
    }
    public boolean updateUser(int id,  String password, String gender, int age, String mobile){
        int count=0;
        Connection conn=DBUtils.getConnection();
        String update_sql="update cm_user set mobile=?,age=?,gender=?,password=? where id=?";
        try{
            PreparedStatement pstmt=conn.prepareStatement(update_sql);
            pstmt.setString(1, mobile);
            pstmt.setInt(2, age);
            pstmt.setString(3, gender);
            pstmt.setString(4, password);
            pstmt.setInt(5, id);

            count = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count > 0;
    }

//   添加用户名是否存在

    public boolean isDupname(String name){
        int count=0;
        Connection conn=DBUtils.getConnection();
        String sql="select * from cm_user where name=?";
        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,name);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                count = 1;
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        }
        return count>0;

    }

    public boolean addUser(String username,String password,String gender,int age,String mobile){
        int result=0;
        Connection conn=DBUtils.getConnection();
        String insert_sql="insert into cm_user values(null,?,?,?,?,?)";
        try {
            PreparedStatement pstmt=conn.prepareStatement(insert_sql);
            pstmt.setString(1,username);
            pstmt.setString(2,password);
            pstmt.setString(3,mobile);

            pstmt.setInt(4,age);
            pstmt.setString(5,gender);
            result=pstmt.executeUpdate();

        }catch (SQLException e){
            e.printStackTrace();
        }
        return result==1;
    }
    public  User getUserById(int id){
        User user=null;
        Connection conn=DBUtils.getConnection();
        String sql="select* from cm_user where id=?";
        try {
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);

            ResultSet rs=pstmt.executeQuery();
            if (rs.next()){
                String username= rs.getString("name");
                String password=rs.getString("password");

                String mobile=rs.getString("mobile");
                int age=rs.getInt("age");
                String gender=rs.getString("gender");

                user=new User(id,username,password, age, gender, mobile);

            }
            DBUtils.closeConnection(conn);
        }catch (SQLException e){
            e.printStackTrace();

        }
        return user;
    }
    public boolean delUser(int id) {
        System.out.println("id:"+id);
        int num=0;
        Connection conn=DBUtils.getConnection();
        String del_sql="delete from cm_user where id=?";
        try {
            PreparedStatement pstmt=conn.prepareStatement(del_sql);
            pstmt.setInt(1,id);
            num=pstmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return num>0;

    }

    public ArrayList<User> getUserByName(String name) {
        ArrayList<User> users=new ArrayList<>();
        Connection conn=DBUtils.getConnection();

        String sql="select * from cm_user where name like '%"+name +"%'";
        try{
           PreparedStatement pstmt=conn.prepareStatement(sql);

            ResultSet rs=pstmt.executeQuery();
            while (rs.next()){
                int id=rs.getInt("id");
                String username=rs.getString(2);
                String password=rs.getString("password");
                int age=rs.getInt("age");
                String gender=rs.getString("gender");
                String mobile=rs.getString("mobile");
                users.add(new User(id,username,password,age,gender,mobile));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return  users;


    }

//    查找用户


}
